﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using System.Data;
using System.Data.SqlClient;
using Lead2012.Models;
using Lead2012.ViewModels;
using Lead2012.Helpers;
using Lead2012.Business;

namespace Lead2012.Repository
{
    public class LeadManagementRepository : BaseManagementRepository
    {
        // Create an instance of entities framework
        Lead2012Entities entities = new Lead2012Entities();

        #region Build Models
        public void GetModel(ref LeadViewModel model)
        {
            model.StatusFilter = GetStatuses();
            model.UserAssignFilter = GetAllUsers();
            model.UserFilter = GetAllUsers();
            model.UserFilter.Add(new ItemFilter() { ID = "0", ItemName = "Chưa được gán" });
            model.LeadSource = GetLeadSource();
            model.LeadType = GetLeadType();
            model.RangeFilter = GetRangeFilter();
            model.LeadWarehouseList = GetAllLeads(ref model);
        }
        public void IgnoreLeadNotification(int userId)
        {
            SqlParameter paramUser = new SqlParameter("@UserId", DbType.Int32);
            paramUser.Value = userId;
            int value = SqlHelper.ExecuteNonQuery(SqlHelper.ConnectionStringProfile, CommandType.StoredProcedure, "IgnoreLeadNotification", paramUser);
            return;
        }
        #endregion

        #region Private methods
        private List<LeadManagementItem> GetAllLeads(ref LeadViewModel model)
        {
            List<LeadManagementItem> list = new List<LeadManagementItem>();

            // FIRST: Get all field system mapping with query
            //      To assign for Name, Phone, Email
            var listFieldSystem = new FieldSystemRepository().GetAll();

            // SECOND: Create a query string
            string fieldName1 = string.Empty, fieldName2 = string.Empty;

            BuildParamsQuery(model, listFieldSystem, ref fieldName1, ref fieldName2);
            model.FieldName1 = fieldName1;
            model.FieldName2 = fieldName2;

            // Filter by keywords            
            if (!string.IsNullOrEmpty(model.Keywords) && !string.IsNullOrEmpty(model.FieldFilter))
            {
                string keyword = model.Keywords.ToLower();

                if (model.FieldFilter.Equals("Name", StringComparison.OrdinalIgnoreCase))
                    model.WhereClause += string.Format(" AND {0} LIKE N'%{1}%'", model.ColumnClause, keyword);
                else if (model.FieldFilter.Equals("Email", StringComparison.OrdinalIgnoreCase))
                    model.WhereClause += string.Format(" AND {0} LIKE N'%{1}%'", model.ColumnClause, keyword);
                else if (model.FieldFilter.Equals("Phone", StringComparison.OrdinalIgnoreCase))
                    model.WhereClause += string.Format(" AND {0} LIKE N'%{1}%'", model.ColumnClause, keyword);
            }

            // Build sql params
            SqlParameter paramFilter = new SqlParameter("@Filters", DbType.String);
            paramFilter.Value = model.Filters;
            SqlParameter paramWhere = new SqlParameter("@Where", DbType.String);
            paramWhere.Value = model.WhereClause;
            SqlParameter paramOrdered = new SqlParameter("@Ordered", DbType.String);
            paramOrdered.Value = model.Ordered;
            SqlParameter paramIndex = new SqlParameter("@PageIndex", DbType.Int32);
            paramIndex.Value = model.PageIndex;
            SqlParameter paramSize = new SqlParameter("@PageSize", DbType.Int32);
            paramSize.Value = model.PageSize;

            // THIRD: Execute a query
            using (SqlDataReader item = SqlHelper.ExecuteReader(SqlHelper.ConnectionStringProfile, CommandType.StoredProcedure, "GetAllLeads", paramFilter, paramWhere, paramOrdered, paramIndex, paramSize))
            {
                while (item.Read())
                {
                    LeadManagementItem lead = new LeadManagementItem();
                    lead.ActionCount = item.IsDBNull(5) ? "0" : item.GetInt32(5).ToString();
                    lead.StatusLog = GetAllLogByLead(item.GetInt32(0));
                    lead.DateAdded = item.GetDateTime(9).ToString("dd/MM/yyyy hh:mm tt");
                    lead.Email = item.IsDBNull(14) ? string.Empty : item.GetString(14);
                    lead.LastAction = item.IsDBNull(11) ? string.Empty : item.GetDateTime(11).ToString("dd/MM/yyyy hh:mm tt");
                    lead.LeadEdit = string.Format("/Lead/Edit/{0}?query={1}", item.GetInt32(0), model.LinkParams);
                    lead.LeadId = item.GetInt32(0);
                    lead.Name = item.IsDBNull(12) ? string.Empty : item.GetString(12);
                    lead.Phone = item.IsDBNull(13) ? string.Empty : item.GetString(13);
                    lead.UserVisible = model.IsAdmin;
                    if (!item.IsDBNull(1))
                    {
                        lead.UserId = item.GetInt32(1);
                        ItemFilter user = model.UserFilter.Where(c=>c.ID.Equals(lead.UserId.ToString())).First();
                        lead.UserName = (user == null) ? "Chưa được gán" : user.ItemName;
                    }
                    if (!item.IsDBNull(2))
                    {
                        lead.StatusId = item.GetInt32(2).ToString();
                        ItemFilter status = model.StatusFilter.Where(c => c.ID.Equals(lead.StatusId)).First();
                        lead.Status = (status == null) ? string.Empty : status.ItemName;
                        lead.Color = (status == null) ? string.Empty : status.CssName;
                    }
                    // VM
                    if (!item.IsDBNull(15))
                    {
                        lead.Vm1 = item.GetString(15);
                    }
                    if (!item.IsDBNull(16))
                    {
                        lead.Vm2 = item.GetString(16);
                    }

                    list.Add(lead);
                }
            }

            // Get total leads
            int totalLeads = 0;
            using (SqlDataReader leads = SqlHelper.ExecuteReader(SqlHelper.ConnectionStringProfile, CommandType.StoredProcedure, "GetAllLeadsCount", paramWhere))
            {
                if (leads.Read())
                {
                    totalLeads = leads.GetInt32(0);
                }
            }

            if (totalLeads > 0)
            {
                model.TotalRecord = totalLeads;
                model.TotalPage = (totalLeads % model.PageSize == 0) ? (totalLeads / model.PageSize) : (totalLeads / model.PageSize + 1);
            }

            return list;
        }

        private List<LeadStatusLongItem> GetAllLogByLead(int leadId)
        {
            List<LeadStatusLongItem> list = new List<LeadStatusLongItem>();

            var items = (from c in entities.LeadStatusLogs
                         join a in entities.LeadStatuses on c.StatusId equals a.StatusId
                         join u in entities.UserSet on c.UserId equals u.ID
                         where c.LeadId == leadId
                         orderby c.LogId descending
                         select new
                         {
                             Id = c.LogId,
                             FirstName = u.FirstName,
                             LastName = u.LastName,
                             LogDate = c.LogDate,
                             Title = a.Title,
                             Note = c.Note
                         }).ToList();

            // Add item to list
            foreach (var item in items)
            {
                LeadStatusLongItem model = new LeadStatusLongItem();
                model.Username = string.Format("{0} {1}", item.FirstName, item.LastName);
                model.DateDecs = item.LogDate.ToString("dd/MM/yyyy hh:mm tt");
                model.Detail = item.Note;
                model.Title = item.Title;
                list.Add(model);
            }


            return list;
        }

        private void BuildParamsQuery(LeadViewModel model, List<FieldSystem> listFieldSystem, ref string fieldName1, ref string fieldName2)
        {
            // 1. Build Filters
            string filters = string.Empty;
            filters = @" [LeadId] ,[UserId] ,[StatusId] ,[SourceId] ,[MilestoneId] ,[ActionCount] ,[CalendarItemCount] ,[LastActionTypeId] ,[LeadFormTypeId] ,[CreateDate] ,[ModifyDate] ,[LastActionDate]";
            if (listFieldSystem != null && listFieldSystem.Count > 0)
            {
                int i = 0;

                // Build Params
                // Build Name
                string paramName = string.Empty;
                foreach (var item in listFieldSystem.Where(c => c.TextColumnName.Equals("Name", StringComparison.OrdinalIgnoreCase)))
                {
                    i++;
                    if (i == 1)
                        paramName += string.Format("f{0}", item.FieldId);
                    else
                        paramName += string.Format("+f{0}", item.FieldId);
                }
                filters += string.Format(",({0}) as Name", paramName);
                if (!string.IsNullOrEmpty(model.FieldFilter) && model.FieldFilter.Equals("Name", StringComparison.OrdinalIgnoreCase))
                    model.ColumnClause = string.Format("({0})", paramName);

                // Bild Phone
                i = 0;
                string paramPhone = string.Empty;
                foreach (var item in listFieldSystem.Where(c => c.TextColumnName.Equals("Phone", StringComparison.OrdinalIgnoreCase)))
                {
                    i++;
                    if (i == 1)
                        paramPhone += string.Format("f{0}", item.FieldId);
                    else
                        paramPhone += string.Format("+f{0}", item.FieldId);
                }
                filters += string.Format(",({0}) as Phone", paramPhone);
                if (!string.IsNullOrEmpty(model.FieldFilter) && model.FieldFilter.Equals("Phone", StringComparison.OrdinalIgnoreCase))
                    model.ColumnClause = string.Format("({0})", paramPhone);

                // Bild Email
                i = 0;
                string paramEmail = string.Empty;
                foreach (var item in listFieldSystem.Where(c => c.TextColumnName.Equals("Email", StringComparison.OrdinalIgnoreCase)))
                {
                    i++;
                    if (i == 1)
                        paramEmail += string.Format("f{0}", item.FieldId);
                    else
                        paramEmail += string.Format("+f{0}", item.FieldId);
                }
                filters += string.Format(",({0}) as Email", paramEmail);
                if (!string.IsNullOrEmpty(model.FieldFilter) && model.FieldFilter.Equals("Email", StringComparison.OrdinalIgnoreCase))
                    model.ColumnClause = string.Format("({0})", paramEmail);
                
                // Bild Vm1
                i = 0;
                string paramVm1 = string.Empty;
                var vm1 = listFieldSystem.Where(c => c.TextColumnName.Equals("Vm1", StringComparison.OrdinalIgnoreCase)).FirstOrDefault();
                if(vm1!=null)
                {
                    fieldName1 = vm1.Tag;
                    filters += string.Format(",(f{0}) as Vm1", vm1.FieldId);
                }                

                // Bild Vm2
                var vm2 = listFieldSystem.Where(c => c.TextColumnName.Equals("Vm2", StringComparison.OrdinalIgnoreCase)).FirstOrDefault();
                if (vm2 != null)
                {
                    fieldName2 = vm2.Tag;
                    filters += string.Format(",(f{0}) as Vm2", vm2.FieldId);
                }  
            }

            // 2. Build Where Clause
            string whereClause = string.Empty;
            whereClause += string.Format(" [LeadId] > 0");
            if (!string.IsNullOrEmpty(model.UserId) && model.UserId != "0")
                whereClause += string.Format(" AND [UserId] IN ({0})", model.UserId);
            if (!string.IsNullOrEmpty(model.StatusId) && model.StatusId != "0")
                whereClause += string.Format(" AND [StatusId] IN ({0})", model.StatusId);
            if (!string.IsNullOrEmpty(model.LeadSourceId) && model.LeadSourceId != "0")
                whereClause += string.Format(" AND [SourceId] IN ({0})", model.LeadSourceId);
            if (!string.IsNullOrEmpty(model.StartDate))
                whereClause += string.Format(" AND  DateAdd(day, datediff(day,0, [CreateDate]), 0) >= '{0}'", model.StartDate);
            if (!string.IsNullOrEmpty(model.EndDate))
                whereClause += string.Format(" AND  DateAdd(day, datediff(day,0, [CreateDate]), 0) <= '{0}'", model.EndDate);
            

            // 3. Build Ordered
            string ordered = string.Empty;
            if (!string.IsNullOrEmpty(model.SortBy))
            {
                switch (model.SortBy.ToUpper())
                {
                    case "NAME":
                        {
                            var fieldItem = listFieldSystem.Where(c => c.TextColumnName.Equals("Name", StringComparison.OrdinalIgnoreCase)).FirstOrDefault();
                            ordered += string.Format(" LeadWarehouse.[{0}] {1}", fieldItem.ValueColumnName, model.OrderBy);
                            break;
                        }
                    case "PHONE":
                        {
                            var fieldItem = listFieldSystem.Where(c => c.TextColumnName.Equals("Name", StringComparison.OrdinalIgnoreCase)).FirstOrDefault();
                            ordered += string.Format(" LeadWarehouse.[{0}] {1}", fieldItem.ValueColumnName, model.OrderBy);
                            break;
                        }
                    case "EMAIL":
                        {
                            var fieldItem = listFieldSystem.Where(c => c.TextColumnName.Equals("Name", StringComparison.OrdinalIgnoreCase)).FirstOrDefault();
                            ordered += string.Format(" LeadWarehouse.[{0}] {1}", fieldItem.ValueColumnName, model.OrderBy);
                            break;
                        }
                    case "DATEADDED":
                        {
                            var fieldItem = listFieldSystem.Where(c => c.TextColumnName.Equals("Name", StringComparison.OrdinalIgnoreCase)).FirstOrDefault();
                            ordered += string.Format(" LeadWarehouse.[{0}] {1}", fieldItem.ValueColumnName, model.OrderBy);
                            break;
                        }
                    case "LASTACTION":
                        {
                            var fieldItem = listFieldSystem.Where(c => c.TextColumnName.Equals("Name", StringComparison.OrdinalIgnoreCase)).FirstOrDefault();
                            ordered += string.Format(" LeadWarehouse.[{0}] {1}", fieldItem.ValueColumnName, model.OrderBy);
                            break;
                        }
                    default:
                        {
                            ordered += string.Format(" LeadWarehouse.[LeadId] desc");
                            break;
                        }
                }
            }

            model.Filters = filters;
            model.WhereClause = whereClause;
            model.Ordered = ordered;
        }
        #endregion
    }
}